import sys
import os

sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from aws_model.athena_submit import submit
from project_utils.get_fridge_mappings import get_duplicate
from config import *
from base_class.base_utils import spend_time
from base_class.inspect_logging import print_to_log
from delete import delete_data


# 源表和目标表
# client = boto3.client('athena')


def list2dict(list_map):
    rs = {}
    for m in list_map:
        if not rs.get(m['original_field']):
            rs[m['original_field']] = {}
        rs[m['original_field']]['value'] = m['natrual_field']
        rs[m['original_field']]['data_type'] = m['data_type']
    return rs


@spend_time("一个型号一天耗时： ")
def insert_fridge_mqtt_mapping(is_hour, mappings, p_date, materials):
    if is_hour:
        source_table = fridge_mqtt_table_hour
        target_table = fridge_mqtt_target_table_hour
    else:
        source_table = fridge_mqtt_table
        target_table = fridge_mqtt_target_table
    for m in materials:
        delete_data(target_table, p_date, m)

    only_result, duplicate_result = get_duplicate(list2dict(mappings))
    # 重复的key与非重复的key对应的新的字段名
    target_columns = [f'"{only_result[k]["value"]}"' for k in only_result.keys()] + [i for i in duplicate_result.keys()]
    # 非重复的字段的数据来源
    source_columns = [f'"COALESCE(TRY(CAST ({k} AS {only_result[k]["data_type"]})),null)"' for k in only_result.keys()]
    # 重复的字段的数据来源
    duplicate_sql = ",".join([f"""COALESCE(TRY(CAST (CASE WHEN {duplicate_result[i]["value2"]} is null THEN
                 {duplicate_result[i]["value1"]} ELSE {duplicate_result[i]["value2"]} END  AS
                 {duplicate_result[i]["data_type"]})),null) as {i}""" for i in duplicate_result])
    if duplicate_sql != '':
        query = f"""INSERT INTO {target_table} (timeStamp, {",".join(target_columns)}) SELECT
                    date_format(from_unixtime(cast(substr(cast(9223372036854775807-cast(substr(rowkey, 8, 19) as bigint) as varchar),1,10) as bigint), 'Asia/Shanghai'), '%Y-%m-%d %H:%i:%s' ) as timeStamp,
                    {",".join(source_columns)}, {duplicate_sql}  FROM {source_table} WHERE"""
    else:
        query = f"""INSERT INTO {target_table} (timeStamp, {",".join(target_columns)}) SELECT
                    date_format(from_unixtime(cast(substr(cast(9223372036854775807-cast(substr(rowkey, 8, 19) as bigint) as varchar),1,10) as bigint), 'Asia/Shanghai'), '%Y-%m-%d %H:%i:%s' ) as timeStamp,
                    {",".join(source_columns)}  FROM {source_table} WHERE"""

    query = query.replace('"', "")
    query += f" p_date = '{p_date}' "
    query += f''' and material in ({"'" + "','".join(materials) + "'"}) '''
    result = submit(query)
    print_to_log(p_date, result)
